package com.etc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.etc.pojo.ScoreInfo;
import com.etc.utils.DBUtil;


public class ScoreInfoDaoImpl implements ScoreInfoDao {
	Map<String, Object> hashMap = null;
	List<Map<String, Object>> list = null;
	@Override
	public List<Map<String, Object>> queryScoreDetailBySno(String studentNo) {
		
		ResultSet rs = DBUtil.doQuery("select a.student_no,student_name,course_name,course_score,score_id,score from tab_student as a left join tab_score as b on a.student_no = b.student_no left join  tab_course as c on b.course_no = c.course_no where a.student_no = ?", studentNo);
		list = new ArrayList<>();
		hashMap = new HashMap<>();
		try {
			while (rs.next()) {
				Integer score_id = rs.getInt("score_id");
				String studentName = rs.getString("student_name");
				String courseName = rs.getString("course_name");
				Float courseScore = rs.getFloat("course_score");
				Float score = rs.getFloat("score");
				hashMap.put("score_id", score_id);
				hashMap.put("student_no", studentNo);
				hashMap.put("student_name", studentName);
				hashMap.put("course_name", courseName);
				hashMap.put("course_score", courseScore);
				hashMap.put("score", score);
				list.add(new HashMap<String, Object>(hashMap));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public List<Map<String, Object>> queryScoreDetailByCname(String courseName) {
		
		ResultSet rs = DBUtil.doQuery("select a.student_no,student_name,course_name,course_score,score_id,score from tab_student as a left join tab_score as b on a.student_no = b.student_no left join  tab_course as c on b.course_no = c.course_no where c.course_name = ?", courseName);
		list = new ArrayList<>();
		hashMap = new HashMap<>();
		try {
			while (rs.next()) {
				Integer score_id = rs.getInt("score_id");
				String studentNo = rs.getString("student_no");
				String studentName = rs.getString("student_name");
				Float courseScore = rs.getFloat("course_score");
				Float score = rs.getFloat("score");
				hashMap.put("score_id", score_id);
				hashMap.put("student_no", studentNo);
				hashMap.put("student_name", studentName);
				hashMap.put("course_name", courseName);
				hashMap.put("course_score", courseScore);
				hashMap.put("score", score);
				list.add(new HashMap<String, Object>(hashMap));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public Map<String, Object> queryScoreDetailBySnoAndCname(String studentNo, String courseName) {
		
		ResultSet rs = DBUtil.doQuery("select a.student_no,student_name,course_name,course_score,score_id ,score from tab_student as a left join tab_score as b on a.student_no = b.student_no left join  tab_course as c on b.course_no = c.course_no where a.student_no = ? and c.course_name = ?", studentNo,courseName);
		hashMap = new HashMap<>();
		try {
			while (rs.next()) {
				Integer score_id = rs.getInt("score_id");
				String studentName = rs.getString("student_name");
				Float courseScore = rs.getFloat("course_score");
				Float score = rs.getFloat("score");
				hashMap.put("score_id", score_id);
				hashMap.put("student_no", studentNo);
				hashMap.put("student_name", studentName);
				hashMap.put("course_name", courseName);
				hashMap.put("course_score", courseScore);
				hashMap.put("score", score);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return hashMap;
	}

	@Override
	public List<Map<String, Object>> queryScoreDetail() {
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		   .append(" a.student_no")
		   .append(" ,student_name")
		   .append(" ,course_name")
		   .append(" ,course_score")
		   .append(" ,score")
		   .append(" ,score_id")
		   .append(" from tab_student as a inner join tab_score as b on a.student_no = b.student_no")
		   .append(" inner join  tab_course as c on b.course_no = c.course_no");
		ResultSet rs = DBUtil.doQuery(sql.toString());
		list = new ArrayList<>();
		hashMap = new HashMap<>();
		try {
			while (rs.next()) {
				Integer score_id = rs.getInt("score_id");
				String studentNo = rs.getString("student_no");
				String studentName = rs.getString("student_name");
				String courseName = rs.getString("course_name");
				Float courseScore = rs.getFloat("course_score");
				Float score = rs.getFloat("score");
				hashMap.put("score_id", score_id);
				hashMap.put("student_no", studentNo);
				hashMap.put("student_name", studentName);
				hashMap.put("course_name", courseName);
				hashMap.put("course_score", courseScore);
				hashMap.put("score", score);
				list.add(new HashMap<String, Object>(hashMap));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return list;
	}

	
	
	@Override
	public int insert(ScoreInfo scoreInfo) {
		
		return DBUtil.doUpdate("insert into tab_score values(null,?,?,?,NOW())",scoreInfo.getStudent_no(),scoreInfo.getCourse_no(),scoreInfo.getScore());

	}

	
}
